package com.ctg.itrdc.imageconsole.control.ssh.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ctg.itrdc.imageconsole.common.util.PropertiesUtils;
import com.ctg.itrdc.imageconsole.control.ssh.model.HostSystem;
import com.ctg.itrdc.imageconsole.control.ssh.utils.DBUtils;

/**
 * DAO used to manage systems
 */
public class SystemDB {

	public static final String AUTHORIZED_KEYS = "authorized_keys";
	private static Logger log = LoggerFactory.getLogger(SystemDB.class);

	public static final String FILTER_BY_PROFILE_ID = "profile_id";

	public static final String DISPLAY_NM = "display_nm";
	public static final String SORT_BY_NAME = DISPLAY_NM;
	public static final String SORT_BY_USER = "user";
	public static final String SORT_BY_HOST = "host";
	public static final String STATUS_CD = "status_cd";
	public static final String SORT_BY_STATUS = STATUS_CD;

	private SystemDB() {
	}


	/**
	 * returns system by id
	 *
	 * @param id
	 *            system id
	 * @return system
	 */
	public static HostSystem getSystem(Long id) {
		HostSystem hostSystem = null;
		Connection con = null;
		try {
			con = DBUtils.getConn();
			hostSystem = getSystem(con, id);
		} catch (Exception e) {
			log.error(e.toString(), e);
		}finally{
			DBUtils.closeConn(con);
		}

		return hostSystem;
	}

	public static HostSystem getSystem(Connection con, Long id) {
		HostSystem hostSystem = null;
		try {
			PreparedStatement stmt = con.prepareStatement("select * from  system where id=?");
			stmt.setLong(1, id);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				hostSystem = new HostSystem();
				hostSystem.setId(rs.getLong("id"));
				hostSystem.setDisplayNm(rs.getString(DISPLAY_NM));
				hostSystem.setUser(rs.getString("user"));
				hostSystem.setHost(rs.getString("host"));
				hostSystem.setPort(rs.getInt("port"));
				hostSystem.setAuthorizedKeys(rs.getString(AUTHORIZED_KEYS));
				hostSystem.setStatusCd(rs.getString(STATUS_CD));
				hostSystem.setPassword(rs.getString("password"));
			}
			DBUtils.closeRs(rs);
			DBUtils.closeStmt(stmt);
		} catch (Exception e) {
			log.error(e.toString(), e);
		}

		return hostSystem;
	}

	public static void insertOrUpdateSystem(){
		String systemIp = PropertiesUtils.getConfigValue("sshConfig", "systemIp");
    	String systemPort = PropertiesUtils.getConfigValue("sshConfig", "systemPort");
    	String user = PropertiesUtils.getConfigValue("sshConfig", "user");
    	String password = PropertiesUtils.getConfigValue("sshConfig", "password");
    	
    	List<HostSystem> hostSystems = getAllSystems();
    	if (hostSystems==null||hostSystems.size()==0) {
    		HostSystem hostSystem = new HostSystem();
    		hostSystem.setUser(user);
    		hostSystem.setPassword(password);
    		hostSystem.setHost(systemIp);
    		hostSystem.setPort(Integer.parseInt(systemPort));
    		hostSystem.setAuthorizedKeys("~/.ssh/authorized_keys");
    		insertSystem(hostSystem);
		}else {
			HostSystem hostSystem = getAllSystems().get(0);
			hostSystem.setUser(user);
    		hostSystem.setPassword(password);
    		hostSystem.setHost(systemIp);
    		hostSystem.setPort(Integer.parseInt(systemPort));
    		updateSystem(hostSystem);
		}
    }
    
	
	public static Long insertSystem(HostSystem hostSystem) {
		Connection con = null;
		Long userId = null;
		try {
			con = DBUtils.getConn();
			PreparedStatement stmt = con
					.prepareStatement(
							"insert into system (display_nm, user, host, port, authorized_keys, status_cd,password) values (?,?,?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, hostSystem.getDisplayNm());
			stmt.setString(2, hostSystem.getUser());
			stmt.setString(3, hostSystem.getHost());
			stmt.setInt(4, hostSystem.getPort());
			stmt.setString(5, hostSystem.getAuthorizedKeys());
			stmt.setString(6, hostSystem.getStatusCd());
			stmt.setString(7, hostSystem.getPassword());
			stmt.execute();

			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				userId = rs.getLong(1);
			}
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);
		return userId;

	}

	public static void updateSystem(HostSystem hostSystem) {
		Connection con = null;
		try {
			con = DBUtils.getConn();

			PreparedStatement stmt = con
					.prepareStatement("update system set display_nm=?, user=?, host=?, port=?, authorized_keys=?, status_cd=?  where id=?");
			stmt.setString(1, hostSystem.getDisplayNm());
			stmt.setString(2, hostSystem.getUser());
			stmt.setString(3, hostSystem.getHost());
			stmt.setInt(4, hostSystem.getPort());
			stmt.setString(5, hostSystem.getAuthorizedKeys());
			stmt.setString(6, hostSystem.getStatusCd());
			stmt.setLong(7, hostSystem.getId());
			stmt.execute();
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);
	}

	public static void deleteAllSystem() {
		Connection con = null;
		try {
			con = DBUtils.getConn();

			PreparedStatement stmt = con.prepareStatement("delete from system");
			stmt.execute();
			DBUtils.closeStmt(stmt);
		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);
	}
	
	public static void deleteSystem(Long hostSystemId) {
		Connection con = null;
		try {
			con = DBUtils.getConn();

			PreparedStatement stmt = con.prepareStatement("delete from system where id=?");
			stmt.setLong(1, hostSystemId);
			stmt.execute();
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);
	}

	public static List<HostSystem> getAllSystems() {
		List<HostSystem> hostSystemList = new ArrayList<>();
		Connection con = null;
		try {
			con = DBUtils.getConn();
			PreparedStatement stmt = con.prepareStatement("select * from  system");
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				HostSystem hostSystem = new HostSystem();
				hostSystem.setId(rs.getLong("id"));
				hostSystem.setDisplayNm(rs.getString(DISPLAY_NM));
				hostSystem.setUser(rs.getString("user"));
				hostSystem.setHost(rs.getString("host"));
				hostSystem.setPort(rs.getInt("port"));
				hostSystem.setAuthorizedKeys(rs.getString(AUTHORIZED_KEYS));
				hostSystem.setStatusCd(rs.getString(STATUS_CD));
				hostSystem.setPassword(rs.getString("password"));
				hostSystemList.add(hostSystem);
			}
			DBUtils.closeRs(rs);
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}
		DBUtils.closeConn(con);

		return hostSystemList;
	}

	public static List<Long> getAllSystemIds(Connection con) {
		List<Long> systemIdList = new ArrayList<>();
		try {
			PreparedStatement stmt = con.prepareStatement("select * from  system");
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				systemIdList.add(rs.getLong("id"));
			}
			DBUtils.closeRs(rs);
			DBUtils.closeStmt(stmt);

		} catch (Exception e) {
			log.error(e.toString(), e);
		}

		return systemIdList;
	}


}
